package com.asiainfo.kbisheet.util

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.asiainfo.kbisheet.vo.WhereVO;

import groovy.xml.MarkupBuilder;

/**
 * DB2的数据库工具实现
 * @author kyunra
 *
 */
class DBUDB2 extends DBUCommon{
	private static final Log log = LogFactory.getLog(DBUDB2.class)
	
    String getColumnsXML(String dataBaseName,String tableName){
		tableName = tableName.toUpperCase()
    	def sql = getSQLInstance()
        
        def req = """
        	select * from sysibm.syscolumns where tbname = '${tableName}' order by colno
    """
        log.info req.toString()
        
        def writer = new StringWriter()
        def xml = new MarkupBuilder( writer )
        
        xml.columns {
            sql.eachRow( req as String  ) { row ->
                xml.column(name: trim(row.NAME),title: trim(row.REMARKS), longTitle:trim(row.REMARKS), type: trim(row.COLTYPE)) 
            }
        }
    	log.info writer.toString()
        writer.toString()
    }
	
	String getPageSQL(queryVO, columnString,orderString, whereString, from){
		"SELECT * FROM (select ${columnString},rownumber() OVER(${orderString}) as RN from ${from} ${whereString}) AS temp WHERE temp.RN BETWEEN ${queryVO.from} AND ${queryVO.to}"
	}
	
	String toDateString(WhereVO whereVO){
		whereVO.column + " " + whereVO.equalSign + " DATE('" + whereVO.value.substring(0, 4) + "-"+ whereVO.value.substring(4, 6) + "-"+ whereVO.value.substring(6, 8) + "')"
	}
	
	String formatDateColumn(String columnName){
		"rtrim(char(year(${columnName}))) || substr(digits(month(${columnName})),9) || substr(digits(day(${columnName})),9)"
	}
}
